package me
import grails.converters.JSON
import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource
import com.microsoft.sqlserver.jdbc.SQLServerDataSource
import grails.io.IOUtils
import groovy.sql.Sql
import groovy.json.*
import groovy.xml.MarkupBuilder
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.*

import org.apache.poi.xssf.usermodel.*
import oracle.jdbc.pool.OracleDataSource
import net.sourceforge.jtds.jdbcx.JtdsDataSource
import grails.plugins.rest.client.RestBuilder
import org.springframework.util.*
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import java.io.*

class AppController {

	def dataSource
	def startupService

    def index() { 
		println 2343242




    }

	def dowloadall() {
		def applicationPath = request.getSession().getServletContext().getRealPath("")
		def file = new File(applicationPath+"data.xml")
		//response.setContentType("text/xml");
		//response.setContentLength((int)file.bytes.size());
		//response.setHeader("Content-Disposition", "attachment;filename=export.xml");

		//response.outputStream << file.text
		//response.outputStream.flush()
		//response.outputStream.close()
		//IOUtils.closeQuietly(contentStream)

		response.setHeader("Content-disposition", "attachment; filename=export.data");
		render(contentType: "text/plain", text: file.text);

	}

	def importall() {

		request.getMultiFileMap().fileupload.each {
			//println it.bytes.length


			def parser = new XmlParser()
			//String applicationPath = request.getSession().getServletContext().getRealPath("")
			//def doc = parser.parse(applicationPath+"data.xml")
			def doc = parser.parse(new ByteArrayInputStream(it.bytes))
			//println doc.dataSources[0]
			//println doc.chartThemes[0]
			println doc.tableThemes[0]
			//println doc


		}


		def results = [success:true]
		render results as JSON

	}

	def exportall() {
		def sql = new Sql(dataSource)
		def dataSources = sql.rows("select * from data_source_hy_info")
		def chartThemes = sql.rows("select * from chart_theme")
		def tableThemes = sql.rows("select * from table_theme")
		def chartFiles = sql.rows("select * from chart_file")

		String applicationPath = request.getSession().getServletContext().getRealPath("")

		def mb = new MarkupBuilder(new File(applicationPath+"data.xml").newPrintWriter())
		//def mb = new MarkupBuilder()
		mb.data() {
			mb.dataSources() {
				dataSources.each{data->

					mb.dataSource() {
						id(data.id)
						userloginid(data.userloginid)
						ds_type(data.ds_type)
						userpassword(data.userpassword)
					}

				}
			}
			mb.chartThemes() {

				chartThemes.each{data->

					mb.chartTheme() {
						id(data.id)
						themeName(data.theme_name)
						themeSource(data.theme_source)
						themeConfigSource(raw(data.theme_config_source))
					}

				}


			}

			mb.tableThemes() {

				tableThemes.each{data->

					mb.tableTheme() {
						id(data.id)
						themeName(data.theme_name)
						themeSource(data.theme_source)
					}

				}


			}

		}

		println mb


		def results = [success:true]
		render results as JSON
	}

	def test() {
		/*def a = JSONObject.fromObject( "{a:function(){}}" );  
		println a
		def b = [a:222]
		render(contentType:"text/json") {
			[
				'results': '1111'
			]
		}*/
		//def sql = new Sql(dataSource)
		//def wb = new XSSFWorkbook(new FileInputStream( new File("d://1.xlsx")))
		//def sheet = wb.getSheetAt(2);
		//println sheet	
		
		//def monthRow = sheet.getRow(0);
		//println getValue(HSSFDateUtil.isCellDateFormatted(cell));
		/*for(int i=1;i<=2;i++) {
			def row = sheet.getRow(i);
			if (row==null) {
				
			} else {
				
				def cell = row.getCell(0);
				def type = getValue(cell,wb)
				
				for(int j=1;j<=12;j++) {
					def month = getValue(monthRow.getCell(j),wb)
					for(int k=1;k<=12;k++) {
						cell = row.getCell(j);
						//def eva= new XSSFFormulaEvaluator(wb);
						//def cellVal = eva.evaluate(cell);
						//println cellVal.getNumberValue()
						//println month+" "+type+" "+cellVal.getNumberValue()*100
						println cell
						if (cell)
						 	sql.execute("insert into etl_table7 values(?,?,?,?,?)",[2018,j,month,type,getValue(cell,wb)])
						else
							sql.execute("insert into etl_table7 values(?,?,?,?,?)",[2018,j,month,type,null])
					}
				}
				
			}
			
		}
		
		*/
		//
		//println cell
				

		//def results = [success:true]
		//render results as JSON

	}

	def demo() {

		def sql = new Sql(dataSource)
   		def results = [:]	  
    	def rows = [
			[name:"Jon",age:20,height:160],
			[name:"John",age:30,height:170],
			[name:"Alex",age:40,height:180]
		]
    	 
    	results = [success:true,total:rows.size(),results:rows]


    	render results as JSON

	}

    def getValue = { cell,wb->
		String value = "";
		switch (cell.getCellType().getCode()) {
			case 1:
				value = String.valueOf(cell.getRichStringCellValue().getString());
				//System.out.print("|");
				break;
			case 0:
				if (DateUtil.isCellDateFormatted(cell)) {
					value = String.valueOf(String.valueOf(cell.getDateCellValue()));
				} else {
					value = String.valueOf(cell.getNumericCellValue());
				}
				//System.out.print("|");
				break;
			case 2:
				value = String.valueOf(cell.getBooleanCellValue());
				//System.out.print("|");
				break;
			case 3:
				def evaluator = wb.getCreationHelper().createFormulaEvaluator();
				evaluator.evaluateFormulaCell(cell);
				def cellValue = evaluator.evaluate(cell);
				if (cellValue) {
					value = String.valueOf(cellValue.getNumberValue());
				} else {
					value = null
				}
				break;
			case 4:
				value = String.valueOf(cell.getErrorCellValue());
				break;
			default:
				print("")
		}
		return value;
	}

	
    def getremotedata() {
		def json	
        def results = [success:true]
        def url = params.url
		def adapterSource = params.adapterSource
        def rest = new RestBuilder()
        def form = new LinkedMultiValueMap<String, String>()
        def sql = new Sql(dataSource)
		Binding binding = new Binding();
        if (params.id!=null && params.id!="") {
            sql.rows("select * from chart_param where chart_id = ?",params.id as Integer).each{
                form.add(it.param_name,it.param_default)
				url = url.replace("\${"+it.param_name+"}","${it.param_default}")
				binding.setVariable(it.param_name, it.param_default);
            }
        }
		binding.setVariable("url", url);
		
		if (adapterSource==null || adapterSource=="") {
			def resp = rest.post(url){
				//auth System.getProperty("artifactory.user"), System.getProperty("artifactory.pass")
				//contentType "application/vnd.org.jfrog.artifactory.security.Group+json"
				/*json {
					name = "test-group"
					description = "A temporary test group"
				}*/
				accept("application/json")
				contentType("application/x-www-form-urlencoded")
				body(form)
			}

			json = resp.json
			
		} else {
			try {
				GroovyShell shell = new GroovyShell(binding);
				Object value = shell.evaluate(params.adapterSource);
				json = value
			} catch (e) {
				println e
				results = [success:false,message:e.message]
			}
		}
		
		
		

        

        render json as JSON
    }

              
    def upload() {
    	def results = [success:true]
    	def fileName = params.excelFile.getOriginalFilename()
    	println fileName
    	def wb
    	if (fileName.indexOf("xlsx")!=-1) {
    		wb = new XSSFWorkbook(params.excelFile.getInputStream())
        } else {
    		wb = new HSSFWorkbook(params.excelFile.getInputStream())
		}
		def data = [:]
		for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
			def sheet = wb.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}

			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				def row = sheet.getRow(rowNum);
				if (row == null) {
					continue;
				}
				
				//System.out.println("多少列" + row.getLastCellNum());
				for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
					def cell = row.getCell(cellNum);
					if (cell == null) {
						continue;
					}
					
					def cellValue = getValue(cell, wb);
					if(cellValue!=null&&cellValue!="") {
						data[rowNum+","+cellNum] = cellValue
					}
				}
				
			}

		}

		def fieldRowFlag
		def fieldColFlag
		def column = []
		def rows = []

		data.each{
			if (fieldRowFlag==null) {
				fieldRowFlag = it.key.split(",")[0] as Integer
			}
			
			if (fieldColFlag==null) {
				fieldColFlag = it.key.split(",")[1] as Integer
			}

			if (it.key.indexOf(fieldRowFlag+",")!=-1) {
				column.add(it.value) 
			}

			if ((it.key.split(",")[0] as Integer)>fieldRowFlag) {
				def rowFlag = (it.key.split(",")[0] as Integer)-fieldRowFlag
				def colFlag = (it.key.split(",")[1] as Integer)-fieldColFlag
				if (rows.size()<rowFlag) {
					def hm = [:]
					hm[column[colFlag]] = it.value
					rows.add(hm)
				} else {
					rows[rows.size()-1][column[colFlag]] = it.value
				}
			}

			
		}
		println rows
		results.rows = rows
		results.cols = column

    	render results as JSON
    }

    def setdetailmode() {
    	def results = [success:true]
    	def sql = new Sql(dataSource)
    	def id = params.id
    	def detailMode = params.detailMode
    	if (detailMode!=null && detailMode!="") {
    		def rows = sql.rows("select * from base_attrs_info where pkid=?",id)
    		if (rows.size()>0) {
    			def detailFlag = rows[0].detail_flag==null?"":rows[0].detail_flag
    			if (detailFlag.indexOf(detailMode)==-1) {
    				sql.execute("update base_attrs_info set detail_flag=concat(ifnull(detail_flag,''),?) where pkid=?",[detailMode,id])
    			}
    		}
    	} else {
    		sql.execute("update base_attrs_info set detail_flag=null where pkid=?",id)
    	}
    	render results as JSON
    }

    def reloadcache() {
    	def results = [success:true]
    	startupService.init()
    	render results as JSON
    }

	def reloadchartcache() {
		def results = [success:true]
		startupService.getViewChartCache()
		render results as JSON
	}


    def builddataset() {
   		def results = [success:true]
    	def rows = []
    	def ds 


    	
    	def sql = new Sql(dataSource)
    	def chartSql = params.chartSql==null?"":params.chartSql
    	def dataSourceInfo = sql.rows("select * from data_source_hy_info where id = ?",params.dataSourceId)[0]
    	def chartParam = []
    	if (params.chartId!=null && params.chartId!="") {
    		chartParam = sql.rows("select * from chart_param where chart_id = ?",params.chartId as Integer)
    	}
    	def ip = dataSourceInfo.ip
    	def db = dataSourceInfo.db
    	def dsType = dataSourceInfo.ds_type
    	def port = dataSourceInfo.port
    	def userName = dataSourceInfo.userloginid
    	def password = dataSourceInfo.userpassword
		
		if (dsType=="MySql") {
    		ds = new MysqlConnectionPoolDataSource();
    		ds.setUser(userName);
			ds.setPassword(password);
			ds.setURL("jdbc:mysql://"+ip+":"+port+"/"+db+"?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8");
    	}

    	if (dsType=="Oracle") {
    		ds = new OracleDataSource();
        	ds.setDriverType("thin");
        	ds.setNetworkProtocol("tcp");
        	ds.setServerName(ip);
        	ds.setDatabaseName(db);
        	ds.setPortNumber(port as Integer);
        	ds.setUser(userName); 
        	ds.setPassword(password);
    	}

    	if (dsType=="SqlServer") {
    		/*ds = new SQLServerDataSource();
    		ds.setUser(userName);
        	ds.setPassword(password);
        	ds.setServerName(ip);
        	ds.setPortNumber(port as Integer);
        	ds.setDatabaseName(db);*/
    		ds = new JtdsDataSource();
	        ds.setServerName(ip);
	        ds.setDatabaseName(db);
	        ds.setUser(userName);
	        ds.setPassword(password);
    	}
		

		def dSql = new Sql(ds)
		def cols = []
    	def printColNames = { meta ->
			(1..meta.columnCount).each {
				cols.add(meta.getColumnLabel(it))
			}
		}

		def printRow = { row ->
			def hm = [:]
			row.toRowResult().keySet().each{
				hm[it] = row[it]
			}
			//println hm
			rows.add(hm)
     	}
     	results.cols = cols
     	results.rows = rows

     	println chartSql
     	if (chartParam.size()>0) {
     		chartParam.each{
     			if (it.param_type=="string") {
					chartSql = chartSql.replace("\${"+it.param_name+"}","'${it.param_default}'")
				}
				if (it.param_type=="number") {
					chartSql = chartSql.replace("\${"+it.param_name+"}","${it.param_default}")
				} 
     		}
     	}
     	println chartSql

        def engine = new groovy.text.SimpleTemplateEngine()
        def binding = [:]

        chartSql = chartSql.replace('\$','')
        
        def template = engine.createTemplate(chartSql).make(binding)
        chartSql = template.toString()

     	try {
    		dSql.eachRow(chartSql, printColNames, printRow)
            results.chartSql = chartSql
		} catch (e) {
			results = [success:false,message:e.getMessage()]
			println e.getMessage()
		}
    	render results as JSON
		
    }

    def configdesc() {
    	def sql = new Sql(dataSource)
    	def row = sql.rows("select * from base_attrs_info where pkid = ?",params.id)[0]
    	//println row
    	[results:row]
    }


    def filterCategoryBand={children,detailMode->
    	def filterCategory = [
    	]
    	children.each{
    		def bl = true

    		if (it.detailFlag!=null && it.detailFlag!="" && detailMode!=null && detailMode!="") {
    			bl = bl && (it.detailFlag.indexOf(detailMode)==-1)
    		}

    		def hm = it.clone()
			hm.visible = bl
			hm.children = filterCategoryBand(hm.children,detailMode)
    		filterCategory.add(hm)

    	}

    	return filterCategory
    }

    def chartband() {
    	def slurper = new JsonSlurper()
    	def sql = new Sql(dataSource)
    	//def temp = Util.chartBand
    	def results = []
    	def detailMode = params.detailMode
    	def chart
    	println params.chartId
    	def chartSource
    	if(params.chartId!=null) {
    		chart = sql.rows("select * from chart_file where id=?",params.chartId as Integer)
            if (chart[0].chart_source==null||chart[0].chart_source=="") {
                chartSource = [:]
            } else {
            	def tempStr = chart[0].chart_source as String
            	tempStr = tempStr.replace("null","\"null\"")
				chartSource = JSONObject.fromObject(tempStr);  
				println 11111
				println chartSource
				println 2222
                //chartSource = slurper.parseText(chart[0].chart_source as String)    
            }
    		
    	}
    	 
    	//println chartSource  
    	Util.chartBand.each{
    		if(it.id=='categoryroot') { 
    			def filterCategory = [
    			]

    			it.children.each{it1->

    				println it1.jsonStr
    				def bl = it1.jsonStr!='dataset'&&it1.jsonStr!='Series'&&it1.jsonStr!='timeline'
    				
    				if (chart!=null) {
    					if (chartSource[it1.jsonStr]!=null) {
    						bl = bl && true
    					} else {
    						bl = bl && (it1.jsonStr!='parallel'&&it1.jsonStr!='parallelAxis'&&it1.jsonStr!='parallelAxis'
	    					&&it1.jsonStr!='radiusAxis'&&it1.jsonStr!='singleAxis'&&it1.jsonStr!='xAxis'&&it1.jsonStr!='yAxis'
	    					&&it1.jsonStr!='polar'&&it1.jsonStr!='geo'&&it1.jsonStr!='calendar'&&it1.jsonStr!='visualMap'
	    					&&it1.jsonStr!='angleAxis'&&it1.jsonStr!='radar'
							)
						}
    				} else {
    					bl = bl && (it1.jsonStr!='parallel'&&it1.jsonStr!='parallelAxis'&&it1.jsonStr!='parallelAxis'
    					&&it1.jsonStr!='radiusAxis'&&it1.jsonStr!='singleAxis'&&it1.jsonStr!='xAxis'&&it1.jsonStr!='yAxis'
    					&&it1.jsonStr!='polar'&&it1.jsonStr!='geo'&&it1.jsonStr!='calendar'&&it1.jsonStr!='visualMap'
    					&&it1.jsonStr!='angleAxis'&&it1.jsonStr!='radar'
						)
    				}

    				if (it1.detailFlag!=null && it1.detailFlag!="" && detailMode!=null && detailMode!="") {
    					bl = bl && (it1.detailFlag.indexOf(detailMode)==-1)
    				}

    				def hm1 = it1.clone()
					hm1.visible = bl
					hm1.children = filterCategoryBand(hm1.children,detailMode)
					filterCategory.add(hm1) 

    				
    			}

    			def hm = it.clone() 
    			hm.children = filterCategory
    			
    			results.add(hm)	
    		} else {
    			results.add(it)	
    		}
    	}
 		
 		

    	render results  as JSON
    	 

    }

    /*def chartprofilestore() {
    	def chartId = params.chartId
    	def sql = new Sql(dataSource)

    	
    	def results = [:]
    	results.text ="."

    	if (chartId) {
    		def chart = sql.rows("select * from chart where id = ?",chartId)[0]
    		def chartSeries = sql.rows("select * from chart_series where chart_id = ?",chartId)
			
			def childrens = []
			childrens.add([
				id:"DataSource",
		    	text:"数据源",
				leaf:true
			])

			chartSeries.eachWithIndex{it,i->
				childrens.add([
					id:"chartSerie"+it.id,
			    	text:it.serie_name+"-"+serieTypeMap[it.serie_type]+"\u56fe", 
					leaf:true
				])
			}

           

			results.children = [ 
	    		[
	    			id:chart.id,
	    			text:chart.file_name,
					leaf:false,
					children:childrens
	    		]
	    	]
    	} else {
    		results.children = []
    	}

    	
    	
    	 
    	render results  as JSON


    }*/


    def createchart() {
    	def sql = new Sql(dataSource)
    	sql.execute("insert into chart(chart_name) values(?)",params.name)
    	def lastId=sql.rows("select last_insert_id() lastid")[0].lastid
    	def results = [message:0]
    	results.lastId = lastId
    	render results  as JSON
    }

	def createserie() {
    	def sql = new Sql(dataSource)
    	sql.execute("insert into chart_series(serie_type,chart_id,serie_name) values(?,?,?)",
    		[params.serieType,params.chartId,params.serieName])
    	def lastId=sql.rows("select last_insert_id() lastid")[0].lastid
    	def row = sql.rows("select * from chart_series where id=?",lastId)[0]
    	def results = [message:0]
    	results.lastId = lastId
    	results.serieName = row.serie_name
    	render results  as JSON
    }


    

    def parseSubOption = {it,options->
    	def hm = [:]
	    hm.optionType = "string"
	    if (it.type=="\"number\"") {
			hm.optionType = "number"
		}
		if (it.type=="\"boolean\"") {
			hm.optionType = "boolean"
		}
		if (it.type=="\"Color\"") {
			hm.optionType = "color"
		}

        if (it.type=="\"number\",\"string\",\"Array\"") {
            hm.optionType = "number,string,array"
        }

		//hw.optionParent = 
	    hm.optionName = it.name
	    hm.optionId = it.ywname
	    hm.optionJson = it.json_str
	    hm.optionValue = it.value.replace("'","").replace("\"","")
	    options.add(hm)

	    mapSeriesType.keySet().each{it1->
	    	//println it1
	    	if (it.json_str.indexOf("Series:Series"+it1+":")!=-1) {
	    		if (!seriesOption.containsKey(mapSeriesType[it1])) {
	    			seriesOption[mapSeriesType[it1]] = []
	    		} else {
	    			seriesOption[mapSeriesType[it1]].add(hm)		
	    		}
		    	
		    }
	    }


    }

    def buildOption = { it,chartOption->
    	if (it.json_str!=null) {
	    	def ja = it.json_str.split(":")
	    	//println it
	    	if (it.value!=null && it.value!="") {
		    	switch (ja.size()) { 
		    		case 1:
		    			chartOption[ja[0]] = it.value.replace("'","");
		    			break;
		    		case 2:
		    			chartOption[ja[0]] = [:]
		    			chartOption[ja[0]][ja[1]] = it.value.replace("'","")
		    			break
		    		case 3:
		    			chartOption[ja[0]] = [:]
		    			chartOption[ja[0]][ja[1]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]] = it.value.replace("'","")
		    			break
		    		case 4:
		    			chartOption[ja[0]] = [:]
		    			chartOption[ja[0]][ja[1]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]][ja[3]] = it.value.replace("'","")
		    			break
		    		case 5:
		    			chartOption[ja[0]] = [:]
		    			chartOption[ja[0]][ja[1]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]][ja[3]] = [:]
		    			chartOption[ja[0]][ja[1]][ja[2]][ja[3]][ja[4]] = it.value.replace("'","")
		    			break
		    		default:
		    			break		
		    	}
		    }
		}
    }


    def mapSeriesType = [:]

    def seriesOption = [:]

    def serieTypeMap = ['line':'\u6298\u7ebf','bar':'\u67f1\u72b6','pie':'\u997c']

    
    def edit() {
    	def sql = new Sql(dataSource)
    	def slurper = new JsonSlurper()

		//sql.execute("alter table chart_file  modify column chart_datasource int(11)")
		/*try {
			sql.execute("alter table remote_adapter drop chart_id")
		} catch(e) {

		}*/
    	println session.userLogin

    	sql.rows("select * from base_attrs_info where json_str like 'Series:Series_:type' or json_str like 'Series:Series__:type'").each{
    		def startIndex = it.json_str.indexOf("Series:Series")+13
    		def endIndex = it.json_str.indexOf(":type")
    		mapSeriesType[it.json_str.substring(startIndex,endIndex)] = it.value.replace("'","")
    		//println it.json_str.substring(startIndex,endIndex)
    	}

    	def chartOption = [:]
        def chartEvent = []
    	def chartSeries = []
    	def chartSource = JsonOutput.prettyPrint(JsonOutput.toJson([
            legend:[:],
            tooltip:[:],
            series:[]
        ]))
    	def chart = null
    	def themeSource
    	def themeName

		def tableThemeSource = ""
    	def tableThemeName = ""

		def remoteAdapterSource = ""

        def chartParam = []
		def chartOptionDataset = [:]

    	if (params.id) {
            chartParam = sql.rows("select * from chart_param where chart_id = ?",params.id as Integer)
    		chartSeries = sql.rows("select * from chart_series where chart_id = ?",params.id as Integer)
            chartEvent = sql.rows("select * from chart_event where chart_id = ?",params.id as Integer)
    		def charts = sql.rows("select * from chart_file where id = ?",params.id)
    		if (charts.size()!=0) {
    			chart = charts[0]
    			if (chart.theme_id!=null&&chart.theme_id!="") {
    				def chartThemes = sql.rows("select * from chart_theme where id = ?",chart.theme_id)	
    				if (chartThemes.size()!=0) {
    					//themeSource = slurper.parse(chartThemes[0].theme_source as String)
    					themeSource = chartThemes[0].theme_source
    					themeName = chartThemes[0].theme_name
    				}
    			}

				if (chart.table_theme_id!=null&&chart.table_theme_id!="") {
    				def tableThemes = sql.rows("select * from table_theme where id = ?",chart.table_theme_id)	
    				if (tableThemes.size()!=0) {
    					tableThemeSource = tableThemes[0].theme_source
    					tableThemeName = tableThemes[0].theme_name
    				}
    			}

				if (chart.remote_adapter_id!=null&&chart.remote_adapter_id!="") {
    				def remoteAdapters = sql.rows("select * from remote_adapter where id = ?",chart.remote_adapter_id)	
    				if (remoteAdapters.size()!=0) {
    					remoteAdapterSource = remoteAdapters[0].adapter_source
    				}
    			}

    			if (chart.chart_source==null||chart.chart_source=="") {
 				   chartSource = JsonOutput.prettyPrint(JsonOutput.toJson([
                        legend:[:],
                        tooltip:[:],
                        series:[]
                    ]))
                } else {
                   chartSource = chart.chart_source
                }
 				println chartSource
    			if (chart.chart_sql && chart.chart_datasource!=-1 && chart.chart_datasource!=-2) {
    				chartOptionDataset = [:]
    				chartOptionDataset.dimensions = []
    				chartOptionDataset.source = []
    				def printColNames = { meta ->
					    (1..meta.columnCount).each {
					        //print meta.getColumnLabel(it).padRight(10)
					        chartOptionDataset.dimensions.add(meta.getColumnLabel(it))
					    }
					}

					def printRow = { row ->
						def hm = [:]
						row.toRowResult().keySet().each{
							hm[it] = row[it]
						}
						chartOptionDataset.source.add(hm)
					    //row.toRowResult().values().each{ print it.toString().padRight(10) }
     				}
     				try {
    					sql.eachRow(chart.chart_sql, printColNames, printRow)
    				} catch(e) {

    				}	
    			}
    			
				if (chart.chart_datasource==-2) {
					def remoteUrl = chart.remote_url?chart.remote_url:""
					Binding binding = new Binding();
					chartParam.each{
						remoteUrl = remoteUrl.replace("\${"+it.param_name+"}","${it.param_default}")
						binding.setVariable(it.param_name, it.param_default);
					}
					binding.setVariable("url", remoteUrl);
					
					chartOptionDataset = [:]
    				chartOptionDataset.dimensions = []
    				chartOptionDataset.source = []
					def rest = new RestBuilder()
					def form = new LinkedMultiValueMap<String, String>()
					if (chart.remote_adapter_id==null||chart.remote_adapter_id=="") {
						sql.rows("select * from chart_param where chart_id = ?",params.id as Integer).each{
							form.add(it.param_name,it.param_default)
						}
						try {
							def resp = rest.post(remoteUrl){
								accept("application/json")
								contentType("application/x-www-form-urlencoded")
								body(form)
							}
							resp.json.results.each{
								if (chartOptionDataset.dimensions.size()==0) {
									it.each{
										chartOptionDataset.dimensions.push(it.key)
									}
								}
								chartOptionDataset.source.add(it)
							}
						} catch (e) {
							println e
						}
					} else {
						
						try {
							GroovyShell shell = new GroovyShell(binding);
							Object value = shell.evaluate(params.adapterSource);
							value.results.each{
								if (chartOptionDataset.dimensions.size()==0) {
									it.each{
										chartOptionDataset.dimensions.push(it.key)
									}
								}
								chartOptionDataset.source.add(it)
							}
							
						} catch (e) {
							println e
							
						}
					}
					
					
					
				}
    			
    				




    		}
    	}

    	def optionCategories = sql.rows("select * from option_category")
    	/*def options = sql.rows(""" SELECT * FROM base_attrs_info 
    	where parent_id = '' and type='\"Object\"' """)*/

    	optionCategories = [
    		[category_name:'']

    	]
    	
    	

		def chartConfigOptionTabs = []
		
		

    	def optionTabPanels = [
    		[
    			id:'chartConfig',
    			optionTabs:chartConfigOptionTabs
    		]
    		
    	]


    	chartSeries.each{
    		def hm = [:]
    		hm.id = "seriesConfig"+it.id
    		def category = [] 
    		category.add([name:"",options:seriesOption[it.serie_type]])	

    		def seriesConfigOptionTabs = []
    		seriesConfigOptionTabs.add(
	    		[
					name:it.serie_name,
					category:category
				]
	    	)
    		hm.optionTabs = seriesConfigOptionTabs
    		optionTabPanels.add(hm)


    	}
   
    	[dataSourceDataset:JsonOutput.toJson(chartOptionDataset),chartEvent:chartEvent,
		chartParam:JsonOutput.toJson(chartParam),
		themeName:themeName,themeSource:themeSource,
		tableThemeName:tableThemeName,tableThemeSource:tableThemeSource,remoteAdapterSource:remoteAdapterSource,
		chartSource:chartSource,chartOption:JsonOutput.toJson(Util.chartOption),chartBand:JsonOutput.toJson(Util.chartBand),chart:chart]

	}

    def designer() {
    	def sql = new Sql(dataSource)


    	sql.rows("select * from base_attrs_info where json_str like 'Series:Series_:type' or json_str like 'Series:Series__:type'").each{
    		def startIndex = it.json_str.indexOf("Series:Series")+13
    		def endIndex = it.json_str.indexOf(":type")
    		mapSeriesType[it.json_str.substring(startIndex,endIndex)] = it.value.replace("'","")
    		//println it.json_str.substring(startIndex,endIndex)
    	}

    	def chartOption = [:]

    	def chartSeries = []

    	def chart = null
    	if (params.id) {

    		chartSeries = sql.rows("select * from chart_series where chart_id = ?",params.id)

    		def charts = sql.rows("select * from chart where id = ?",params.id)
    		if (charts.size()!=0) {
    			chart = charts[0]
    			def slurper = new JsonSlurper()
 				chartOption = slurper.parseText(chart.chart_source)

    			if (chart.chart_sql) {
    				chartOption.dataset = [:]
    				chartOption.dataset.dimensions = []
    				chartOption.dataset.source = []
    				def printColNames = { meta ->
					    (1..meta.columnCount).each {
					        //print meta.getColumnLabel(it).padRight(10)
					        chartOption.dataset.dimensions.add(meta.getColumnLabel(it))
					    }
					}

					def printRow = { row ->
						def hm = [:]
						row.toRowResult().keySet().each{
							hm[it] = row[it]
						}
						chartOption.dataset.source.add(hm)
					    //row.toRowResult().values().each{ print it.toString().padRight(10) }
     				}

    				sql.eachRow(chart.chart_sql, printColNames, printRow)
    					
    			}
    			

    			
    				




    		}
    	}

    	def optionCategories = sql.rows("select * from option_category")
    	/*def options = sql.rows(""" SELECT * FROM base_attrs_info 
    	where parent_id = '' and type='\"Object\"' """)*/

    	optionCategories = [
    		[category_name:'']

    	]
    	
    	def options = sql.rows(""" SELECT * FROM base_attrs_info 
    	where parent_id = '' """)
    	

		def chartConfigOptionTabs = []
		
		

		//println options.size()
		def subOptions
		options.each{
					subOptions = []
					//println it
					//buildOption(it,chartOption)
    				def rows = sql.rows(" SELECT * FROM base_attrs_info where parent_id = ? ",it.pkid)
	    			rows.each{it2->
	    				//buildOption(it2,chartOption)
	    				def rows2 = sql.rows(" SELECT * FROM base_attrs_info where parent_id = ? ",it2.pkid)
		    			rows2.each{it3->
		    				//buildOption(it3,chartOption)
		    				def rows3 = sql.rows(" SELECT * FROM base_attrs_info where parent_id = ? ",it3.pkid)
			    			rows3.each{it4->
			    				//buildOption(it4,chartOption)
			    				def rows4 = sql.rows(" SELECT * FROM base_attrs_info where parent_id = ? ",it4.pkid)
				    			rows4.each{it5->
				    				//buildOption(it5,chartOption)
				    				def rows5 = sql.rows(" SELECT * FROM base_attrs_info where parent_id = ? ",it5.pkid)
					    			if (rows5.size()==0 ) {
						    			parseSubOption(it5,subOptions)
						    		}
				    			}

			    				if (rows4.size()==0 ) {
					    			parseSubOption(it4,subOptions)
					    		}
			    			}

		    				if (rows3.size()==0 ) {
				    			parseSubOption(it3,subOptions)
				    		}
		    			}

	    				if (rows2.size()==0) {
			    			parseSubOption(it2,subOptions)
			    		}
	    			}
	    			if (rows.size()==0) {
	    				parseSubOption(it,subOptions)
	    			}
    		

	    	def category = []
    		category.add([name:"",options:subOptions])		
    		
    		if (it.ywname!="Series" && it.ywname!="dataset") {
	    		chartConfigOptionTabs.add(
	    			[
						name:it.name,
						category:category
					]
	    		)
    		}



    	}

    	
    	//println chartOption as JSON

    	def optionTabPanels = [
    		[
    			id:'chartConfig',
    			optionTabs:chartConfigOptionTabs
    		]
    		
    	]


    	chartSeries.each{
    		def hm = [:]
    		hm.id = "seriesConfig"+it.id
    		def category = [] 
    		category.add([name:"",options:seriesOption[it.serie_type]])	

    		def seriesConfigOptionTabs = []
    		seriesConfigOptionTabs.add(
	    		[
					name:it.serie_name,
					category:category
				]
	    	)
    		hm.optionTabs = seriesConfigOptionTabs
    		optionTabPanels.add(hm)


    	}

    	[seriesOption:JsonOutput.toJson(seriesOption),chartSeries:chartSeries,panels:optionTabPanels,chart:chart,chartOption:JsonOutput.toJson(chartOption)]

	}

}
